package many

import (
	"fmt"
	"gorm.io/gorm"
	"reflect"
	"strings"
)

// 核心查询函数
// SingleQuery 查询构建器
type SingleQuery struct {
	db           *gorm.DB
	table        interface{}    //查询表-必填
	selectFields []string       //展示字段-可选
	wheres       []whereClause  //条件-可选
	groups       []string       //分组-可选
	havings      []havingClause //分组条件-可选
	orders       []string       //排序-可选
	pageNum      int            //页码-可选
	pageSize     int            //条数-可选
	joins        []joinClause   //关联查询-可选
}

type whereClause struct {
	query interface{}
	args  []interface{}
}

type havingClause struct {
	query interface{}
	args  []interface{}
}

type joinClause struct {
	joinType string
	query    interface{}
	args     []interface{}
}

func NewSingleQuery(db *gorm.DB, table interface{}) *SingleQuery {
	return &SingleQuery{
		db:    db,
		table: table,
		// 设置默认值
		pageNum:  1,
		pageSize: 10,
	}
}

// ---------------------------------------设置参数--------------------------------------------------
// Select()
func (this *SingleQuery) Select(fields ...string) *SingleQuery {
	this.selectFields = fields
	return this
}

// Where()
func (this *SingleQuery) Where(param interface{}, args ...interface{}) *SingleQuery {
	this.wheres = append(this.wheres, whereClause{param, args})
	return this
}

// Group()
func (this *SingleQuery) GroupBy(fields ...string) *SingleQuery {
	this.groups = append(this.groups, fields...)
	return this
}

// Having()
func (this *SingleQuery) Having(param interface{}, args ...interface{}) *SingleQuery {
	this.havings = append(this.havings, havingClause{param, args})
	return this
}

// OrderBy()
func (this *SingleQuery) OrderBy(fields ...string) *SingleQuery {
	this.orders = append(this.orders, fields...)
	return this
}

// OrderBy 降序
func (this *SingleQuery) OrderByDesc(field string) *SingleQuery {
	return this.OrderBy(fmt.Sprintf("%s DESC", field))
}

// OrderBy 升序
func (this *SingleQuery) OrderByAsc(field string) *SingleQuery {
	return this.OrderBy(fmt.Sprintf("%s ASC", field))
}

// Page() 设置分页
func (this *SingleQuery) Page(pageNum, pageSize int) *SingleQuery {
	this.pageNum = pageNum
	this.pageSize = pageSize
	return this
}

// ---------------------------------------关联查询方法--------------------------------------------------
// Joins 通用关联查询
func (this *SingleQuery) Joins(joinType, query string, args ...interface{}) *SingleQuery {
	this.joins = append(this.joins, joinClause{joinType, query, args})
	return this
}

// InnerJoin 内连接
func (this *SingleQuery) InnerJoin(query string, args ...interface{}) *SingleQuery {
	return this.Joins("INNER", query, args...)
}

// LeftJoin 左连接
func (this *SingleQuery) LeftJoin(query string, args ...interface{}) *SingleQuery {
	return this.Joins("LEFT", query, args...)
}

// RightJoin 右连接
func (this *SingleQuery) RightJoin(query string, args ...interface{}) *SingleQuery {
	return this.Joins("RIGHT", query, args...)
}

// CrossJoin 交叉连接
func (this *SingleQuery) CrossJoin(query string, args ...interface{}) *SingleQuery {
	return this.Joins("CROSS", query, args...)
}

// JoinOn 使用 ON 条件的关联查询（简化版）
func (this *SingleQuery) JoinOn(joinType, table, condition string) *SingleQuery {
	query := fmt.Sprintf("%s ON %s", table, condition)
	return this.Joins(joinType, query)
}

// InnerJoinOn 内连接带 ON 条件
func (this *SingleQuery) InnerJoinOn(table, condition string) *SingleQuery {
	return this.JoinOn("INNER", table, condition)
}

// LeftJoinOn 左连接带 ON 条件
func (this *SingleQuery) LeftJoinOn(table, condition string) *SingleQuery {
	return this.JoinOn("LEFT", table, condition)
}

// RightJoinOn 右连接带 ON 条件
func (this *SingleQuery) RightJoinOn(table, condition string) *SingleQuery {
	return this.JoinOn("RIGHT", table, condition)
}

// ---------------------------------------查询函数--------------------------------------------------
// First() 查询一条
func (this *SingleQuery) First() error {
	query := this.buildQuery()
	return query.First(this.table).Error
}

// Find() 查询列表
func (this *SingleQuery) Find() error {
	query := this.buildQuery()
	return query.Find(this.table).Error
}

// Count() 统计条数
func (this *SingleQuery) Count() (int64, error) {
	//构建统计条数sql：	SELECT count(*) FROM ( 【这里放入完整sql 】) AS count_table

	// 构建基础查询（不包含分页和排序）
	baseQuery := this.buildBaseQuery()

	// 统一使用子查询方式统计总数
	// 生成完整的查询SQL（包含SELECT、JOIN、WHERE、GROUP BY、HAVING，但不包含ORDER BY和分页）
	var total int64

	// 使用子查询包装
	subQuery := baseQuery.Select("1") // 使用 SELECT 1 提高性能

	// 执行计数查询
	err := this.db.Table("(?) as count_table", subQuery).Count(&total).Error
	return total, err
}

// buildBaseQuery 构建基础查询（不包含ORDER BY和分页）
func (this *SingleQuery) buildBaseQuery() *gorm.DB {
	query := this.db.Model(this.table)

	// 拼接关联查询
	if len(this.joins) > 0 {
		for _, join := range this.joins {
			query = query.Joins(fmt.Sprintf("%s JOIN %s", join.joinType, join.query), join.args...)
		}
	}

	// 拼接显示字段（在Count中会被覆盖，但保持查询结构完整）
	if len(this.selectFields) > 0 {
		query = query.Select(strings.Join(this.selectFields, ","))
	}

	// 拼接条件
	if len(this.wheres) > 0 {
		for _, where := range this.wheres {
			query = query.Where(where.query, where.args...)
		}
	}

	// 拼接分组
	if len(this.groups) > 0 {
		query = query.Group(strings.Join(this.groups, ","))
	}

	// 拼接分组条件
	if len(this.havings) > 0 {
		for _, having := range this.havings {
			query = query.Having(having.query, having.args...)
		}
	}

	//// 拼接排序
	//if len(this.orders) > 0 {
	//	query = query.Order(strings.Join(this.orders, ","))
	//}

	return query
}

// 构建查询（包含所有条件）
func (this *SingleQuery) buildQuery() *gorm.DB {
	query := this.buildBaseQuery()

	// 拼接排序（只有完整查询才需要排序）
	if len(this.orders) > 0 {
		query = query.Order(strings.Join(this.orders, ","))
	}

	return query
}

// ---------------------------------------分页查询--------------------------------------------------
// 分页结果结构体
type PaginateResult struct {
	Page      int   `json:"page"`       // 当前页码
	PageSize  int   `json:"page_size"`  // 每页大小
	Total     int64 `json:"total"`      // 总记录数
	TotalPage int   `json:"total_page"` // 总页数
}

// 分页获取偏移量（辅助方法）
func (this *SingleQuery) GetOffset() int {
	if this.pageNum <= 0 {
		return 0
	}
	return (this.pageNum - 1) * this.pageSize
}

// 分页获取限制数（辅助方法）
func (this *SingleQuery) GetLimit() int {
	return this.pageSize
}

// Paginate() 分页查询
func (this *SingleQuery) Paginate() (*PaginateResult, error) {
	// 计算总数
	total, err := this.Count()
	if err != nil {
		return nil, err
	}

	// 构建基础查询（不包含分页）
	baseQuery := this.buildQuery()

	// 计算总页数
	totalPage := 0
	if total > 0 && this.pageSize > 0 {
		totalPage = int((total + int64(this.pageSize) - 1) / int64(this.pageSize))
	}

	// 应用分页查询数据
	query := baseQuery
	if this.pageNum > 0 && this.pageSize > 0 {
		offset := (this.pageNum - 1) * this.pageSize
		query = query.Offset(offset).Limit(this.pageSize)
	}

	// 执行查询
	if err := query.Find(this.table).Error; err != nil {
		return nil, err
	}

	// 返回分页结果
	return &PaginateResult{
			Page:      this.pageNum,
			PageSize:  this.pageSize,
			Total:     total,
			TotalPage: totalPage,
		},
		nil
}

// ---------------------------------------函数式泛型（保持函数形式）--------------------------------------------------
//灵活使用：可以在同一个查询表的基础上，返回不同的结构体类型
//FirstTo[T]()：查询单条到指定类型
//FindTo[T]()：查询列表到指定类型
//PaginateTo[T]()：分页查询到指定类型

// FirstTo() 查询一条返回指定T泛型
func FirstTo[T any](this *SingleQuery) (*T, error) {
	query := this.buildQuery()
	var result T
	err := query.First(&result).Error
	if err != nil {
		return nil, err
	}
	return &result, nil
}

// FindTo() 查询列表返回指定T泛型
func FindTo[T any](this *SingleQuery) ([]T, error) {
	query := this.buildQuery()
	var results []T
	err := query.Find(&results).Error
	if err != nil {
		return nil, err
	}
	return results, nil
}

// PaginateTo() 分页查询返回指定T泛型
func PaginateTo[T any](this *SingleQuery) ([]T, *PaginateResult, error) {
	// 计算总数
	total, err := this.Count()
	if err != nil {
		return nil, nil, err
	}

	// 构建基础查询（不包含分页）
	baseQuery := this.buildQuery()

	// 计算总页数
	totalPage := 0
	if total > 0 && this.pageSize > 0 {
		totalPage = int((total + int64(this.pageSize) - 1) / int64(this.pageSize))
	}

	// 应用分页查询数据
	query := baseQuery
	if this.pageNum > 0 && this.pageSize > 0 {
		offset := (this.pageNum - 1) * this.pageSize
		query = query.Offset(offset).Limit(this.pageSize)
	}

	// 执行查询到指定类型
	var results []T
	if err := query.Find(&results).Error; err != nil {
		return nil, nil, err
	}

	// 返回分页结果
	paginateResult := &PaginateResult{
		Page:      this.pageNum,
		PageSize:  this.pageSize,
		Total:     total,
		TotalPage: totalPage,
	}

	return results, paginateResult, nil
}

// ---------------------------------------原生SQL泛型方法--------------------------------------------------
// ConditionBuilder 条件构建器
type ConditionBuilder struct {
	baseSQL string
	wheres  []string
	args    []interface{}
	orderBy string
	groupBy string
	having  string
}

// NewConditionBuilder 创建条件构建器
func NewConditionBuilder(baseSQL string) *ConditionBuilder {
	return &ConditionBuilder{
		baseSQL: baseSQL,
	}
}

// AddCondition 添加条件
func (cb *ConditionBuilder) AddCondition(condition string, value interface{}, include bool) *ConditionBuilder {
	if include && !isEmpty(value) {
		cb.wheres = append(cb.wheres, condition)
		cb.args = append(cb.args, value)
	}
	return cb
}

// AddRange 添加范围条件
func (cb *ConditionBuilder) AddRange(field string, min, max interface{}) *ConditionBuilder {
	if !isEmpty(min) {
		cb.wheres = append(cb.wheres, fmt.Sprintf("%s >= ?", field))
		cb.args = append(cb.args, min)
	}
	if !isEmpty(max) {
		cb.wheres = append(cb.wheres, fmt.Sprintf("%s <= ?", field))
		cb.args = append(cb.args, max)
	}
	return cb
}

// AddLike 添加LIKE条件
func (cb *ConditionBuilder) AddLike(field string, value interface{}, include bool) *ConditionBuilder {
	if include && !isEmpty(value) {
		cb.wheres = append(cb.wheres, fmt.Sprintf("%s LIKE ?", field))
		cb.args = append(cb.args, "%"+toString(value)+"%")
	}
	return cb
}

// AddIn 添加IN条件
func (cb *ConditionBuilder) AddIn(field string, values []interface{}, include bool) *ConditionBuilder {
	if include && len(values) > 0 {
		placeholders := strings.Repeat("?,", len(values)-1) + "?"
		cb.wheres = append(cb.wheres, fmt.Sprintf("%s IN (%s)", field, placeholders))
		cb.args = append(cb.args, values...)
	}
	return cb
}

// SetOrderBy 设置排序
func (cb *ConditionBuilder) SetOrderBy(order string) *ConditionBuilder {
	cb.orderBy = order
	return cb
}

// SetGroupBy 设置分组
func (cb *ConditionBuilder) SetGroupBy(group string) *ConditionBuilder {
	cb.groupBy = group
	return cb
}

// SetHaving 设置HAVING条件
func (cb *ConditionBuilder) SetHaving(having string, args ...interface{}) *ConditionBuilder {
	cb.having = having
	cb.args = append(cb.args, args...)
	return cb
}

// Build 构建完整SQL
func (cb *ConditionBuilder) Build() (string, []interface{}) {
	var sql strings.Builder
	sql.WriteString(cb.baseSQL)

	if len(cb.wheres) > 0 {
		sql.WriteString(" WHERE ")
		sql.WriteString(strings.Join(cb.wheres, " AND "))
	}

	if cb.groupBy != "" {
		sql.WriteString(" GROUP BY ")
		sql.WriteString(cb.groupBy)
	}

	if cb.having != "" {
		sql.WriteString(" HAVING ")
		sql.WriteString(cb.having)
	}

	if cb.orderBy != "" {
		sql.WriteString(" ORDER BY ")
		sql.WriteString(cb.orderBy)
	}

	return sql.String(), cb.args
}

// 辅助函数
func isEmpty(value interface{}) bool {
	if value == nil {
		return true
	}
	v := reflect.ValueOf(value)
	switch v.Kind() {
	case reflect.String, reflect.Array, reflect.Slice, reflect.Map:
		return v.Len() == 0
	case reflect.Ptr:
		return v.IsNil()
	}
	return false
}

func toString(value interface{}) string {
	return fmt.Sprintf("%v", value)
}

// RawFirstTo 原生SQL查询单条记录到指定类型
func RawFirstTo[T any](db *gorm.DB, sql string, args ...interface{}) (*T, error) {
	var result T
	err := db.Raw(sql, args...).First(&result).Error
	if err != nil {
		return nil, err
	}
	return &result, nil
}

// RawFindTo 原生SQL查询多条记录到指定类型
func RawFindTo[T any](db *gorm.DB, sql string, args ...interface{}) ([]T, error) {
	var results []T
	err := db.Raw(sql, args...).Find(&results).Error
	if err != nil {
		return nil, err
	}
	return results, nil
}

// RawPaginateTo 原生SQL分页查询到指定类型
func RawPaginateTo[T any](db *gorm.DB, sql string, pageNum, pageSize int, args ...interface{}) ([]T, *PaginateResult, error) {
	// 计算总数
	countSQL := fmt.Sprintf("SELECT COUNT(*) FROM (%s) as count_table", sql)
	var total int64
	err := db.Raw(countSQL, args...).Count(&total).Error
	if err != nil {
		return nil, nil, err
	}

	// 计算总页数
	totalPage := 0
	if total > 0 && pageSize > 0 {
		totalPage = int((total + int64(pageSize) - 1) / int64(pageSize))
	}

	// 构建分页SQL
	if pageNum > 0 && pageSize > 0 {
		offset := (pageNum - 1) * pageSize
		sql = fmt.Sprintf("%s LIMIT %d OFFSET %d", sql, pageSize, offset)
	}

	// 执行查询
	var results []T
	err = db.Raw(sql, args...).Find(&results).Error
	if err != nil {
		return nil, nil, err
	}

	// 返回分页结果
	paginateResult := &PaginateResult{
		Page:      pageNum,
		PageSize:  pageSize,
		Total:     total,
		TotalPage: totalPage,
	}

	return results, paginateResult, nil
}
